"""
Case Type   : 密态等值安全测试
Case Name   : 通过视图绕过加密数据测试
Create At   : 2023/06/07
Owner       : zou_jialiang0501162244
Description :
    1、配置全密态环境变量
    2、创建客户端主密钥 列加密密钥
    3. 创建普通用户、表和视图，给用户赋select视图权限
    4、普通用户连接查询视图
    5、清理环境
Expect      :
    1、配置成功
    2、成功
    3、成功
    4、报错，failed to decrypt column encryption key
    5、成功
History     :
"""

import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class FullDensityState(unittest.TestCase):

    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.cons = Constant()
        self.userNode = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.cmk = 'cmk_privilege_0029'
        self.cek = 'cek_privilege_0029'
        self.table = 't_privilege_0029'
        self.view = 'v_privilege_0029'
        self.user = 'u_privilege_0029'
        self.key_path = 'keypath_privilege_0029'
        self.localkms = os.path.join(macro.DB_INSTANCE_PATH, '..', 'app',
                                     'etc', 'localkms')

    def test_security(self):
        text = '-----step1:配置$GAUSSHOME/etc/localkms路径;expect:成功-----'
        self.logger.info(text)
        mkdir_cmd = f'if [ ! -d "{self.localkms}" ];' \
                    f'then mkdir {self.localkms}; echo $?;fi'
        self.logger.info(mkdir_cmd)
        res = self.userNode.sh(mkdir_cmd).result()
        self.assertTrue('0' in res, f"执行失败:{text}")

        text = '-----step2:创建客户端主密钥，列加密密钥;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'''drop client master key if exists {self.cmk} cascade;
        drop column encryption key if exists {self.cek} cascade;
        create client master key {self.cmk} with (key_store=localkms,
        key_path="{self.key_path}", algorithm=RSA_3072);
        create column encryption key {self.cek} 
        with values (client_master_key = {self.cmk}, 
        algorithm = AEAD_AES_256_CBC_HMAC_SHA256);'''
        msg = self.pri_sh.execut_db_sql(sql_cmd, sql_type='-C')
        self.logger.info(msg)
        self.assertTrue(self.cons.create_cmk_success in msg and
                        self.cons.create_cek_success in msg, f"执行失败:{text}")

        text = '-----step3:创建普通用户、表和视图，给用户赋select视图权限;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'''drop user if exists {self.user} cascade;
        drop table if exists {self.table} cascade;
        create user {self.user} with password '{macro.COMMON_PASSWD}'; 
        create table {self.table}(id_number int, 
        name text encrypted with (column_encryption_key = {self.cek},
        encryption_type = DETERMINISTIC));
        insert into {self.table} values(1, 'dbsec');
        create or replace view {self.view} as select * from {self.table};
        grant select on {self.view} to {self.user};'''
        msg = self.pri_sh.execut_db_sql(sql_cmd, sql_type='-C')
        self.logger.info(msg)
        self.assertIn(self.cons.TABLE_CREATE_SUCCESS, msg, f"建表失败:{text}")
        self.assertIn(self.cons.CREATE_ROLE_SUCCESS_MSG, msg, f"执行失败:{text}")
        self.assertIn(self.cons.GRANT_SUCCESS_MSG, msg, f"执行失败:{text}")
        self.assertIn(self.cons.INSERT_SUCCESS_MSG, msg, f"执行失败:{text}")
        self.assertIn(self.cons.CREATE_VIEW_SUCCESS_MSG, msg, f"执行失败:{text}")

        text = '-----step4:普通用户查询视图;expect:查询报错-----'
        self.logger.info(text)
        sql_cmd = f"select * from {self.view};"
        self.logger.info(sql_cmd)
        msg = self.pri_sh.execut_db_sql(
            sql_cmd, sql_type=f'-C -U {self.user} -W{macro.COMMON_PASSWD}')
        self.logger.info(msg)
        err_msg = 'ERROR(CLIENT): failed to decrypt column encryption key'
        self.assertIn(err_msg, msg, f"执行失败:{text}")

    def tearDown(self):
        text = '---step5:清理环境 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''drop table if exists {self.table} cascade;
                drop column encryption key {self.cek} cascade; 
                drop client master key {self.cmk} cascade;
                drop user if exists {self.user} cascade;
                delete from pg_catalog.pg_rlspolicy;'''
        self.logger.info(sql_cmd)
        msg = self.pri_sh.execut_db_sql(sql_cmd, sql_type='-C')
        self.logger.info(msg)
        del_cmd = f'rm -rf {self.localkms};'
        self.logger.info(del_cmd)
        del_res = self.userNode.sh(del_cmd).result()
        self.logger.info(del_res)
        self.assertIn(self.cons.TABLE_DROP_SUCCESS, msg, f"执行失败:{text}")
        self.assertIn(self.cons.drop_cek_success, msg, f"执行失败:{text}")
        self.assertIn(self.cons.drop_cmk_success, msg, f"执行失败:{text}")
        self.assertIn(self.cons.DROP_ROLE_SUCCESS_MSG, msg, f"执行失败:{text}")
        self.assertIn(self.cons.DELETE_SUCCESS_MSG, msg, f"执行失败:{text}")
        self.assertEqual('', del_res, f"执行失败:{text}")
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
